package com.siqi.hr.system.common.service;

import com.siqi.hr.common.Constants;
import com.siqi.hr.common.entity.ExportEntity;
import com.siqi.hr.common.utils.ExportUtils;
import com.siqi.hr.common.utils.FileUtils;
import com.siqi.hr.common.utils.RegularExpressionValidation;
import com.siqi.hr.common.utils.TableConstant;
import com.siqi.hr.common.utils.json.JSONArray;
import com.siqi.hr.system.common.dao.intf.ExportImportDao;
import com.siqi.hr.system.common.sql.FieldTypeParse;
import com.siqi.hr.system.custom.dao.intf.EntityTypeDao;
import com.siqi.hr.system.organization.entity.Employee;
import org.apache.commons.collections.map.LinkedMap;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 导入导出通用Service业务层
 * @author     xiaogq
 * @date       2017-01-05
 */
@Service("exportImportService")
@Transactional(rollbackFor={Exception.class}, propagation= Propagation.REQUIRED)
public class ExportImportService {

    @Autowired
    private ExportImportDao exportImportDao;
    @Autowired
    private EntityTypeDao entityTypeDao;

    /**
     * 导出通用方法
     * @author                   xiaogq
     * @param exportEntity       导出数据对象
     * @param emp                当前登录用户信息对象
     */
    public Map<String, String> entityExport(ExportEntity exportEntity, Employee emp) {
        Map<String, String> filesMap = new HashMap<String, String>();
        String path = exportEntity.getUrl();
        //获取系统当前时间
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String filename = sdf.format(new Date());
        Map<String, Object> map = exportImportDao.entityExport(exportEntity, emp);
        if (exportEntity.getKind() == 3) {
            filename = filename + ".csv";
            FileUtils.deletefile(path, filename);
            ExportUtils.exportCsv(map, path,filename, exportEntity.getSplitValue(), exportEntity.getUnicode());
        }else {
            if (exportEntity.getKind() == 1) {
                filename = filename + ".xls";
            } else if (exportEntity.getKind() == 2) {
                filename = sdf.format(new Date()) + ".xlsx";
            }
            FileUtils.deletefile(path, filename);
            ExportUtils.poiEntityExport(map, path,filename);
        }
        filesMap.put("path", path);
        filesMap.put("name", filename);
        return filesMap;
    }

    /**
     * 导入实体数据通用方法
     * @author           xiaogq
     * @param request             HttpServletRequest对象
     * @param entityId           实体id
     * @param imfJsonArray       导入实体字段JSON数据信息
     * @param listDatas          导入文件数据
     * @throws Exception
     */
    public Map<String, Object> importEntityData(HttpServletRequest request, String entityId, JSONArray imfJsonArray, List<Map<String, Object>> listDatas)throws Exception{
        Employee emp = (Employee)request.getSession().getAttribute("emp");
        Map<String, Object> map = new HashMap<String, Object>();
        Boolean boo = true;
        String filename = "",path = "";
        //将要导入的实体字段JSON信息转换成List集合
        List<Map<String,String>> fieldMap = ExportUtils.jsonArrayToList(imfJsonArray);
        //每个引用字段一次性查询Excel文件中的引用字段的名称对应的id及名称
        Map<String, List<Map<String, Object>>> idMap = queryQutoFieldId(listDatas,fieldMap);
        //导入excel数据时将引用字段中文名称替换成对应的id
        List<Map<String, Object>> newDatas = returnInsertListDatas(listDatas,idMap,fieldMap);
        //导入excel数据验证数据是否合法方法
        Map<String,Object> errorDataMap = verlidataImport(listDatas,newDatas,fieldMap,entityId,emp);
        boolean bool = errorDataMap.get("bool") == null ? false : Boolean.valueOf(errorDataMap.get("bool").toString());
        if (bool) {
            path = request.getServletContext().getRealPath("") + Constants.EXPORT_FILE_PATH;
            filename = "importErrorData.xls";
            try {
                FileUtils.deletefile(path,filename);
                ExportUtils.poiEntityExport(errorDataMap,path,filename);
            } catch (Exception e) {
                e.printStackTrace();
                boo = false;
            }
            map.put("path", path);
            map.put("name", filename);
        }
        map.put("boo", boo);
        map.put("bool", bool);
        map.put("successNumber", errorDataMap.get("successNumber"));
        map.put("allNumber", errorDataMap.get("allNumber"));
        return map;
    }

    /**
     * 每个引用字段一次性查询Excel文件中的引用字段的名称对应的id及名称
     * @author             xiaogq
     * @param listDatas    excel文件中读取的数据集合
     * @param fieldMap     引用字信息
     */
    public Map<String, List<Map<String, Object>>> queryQutoFieldId(List<Map<String, Object>> listDatas, List<Map<String, String>> fieldMap){
        Map<String, List<Map<String, Object>>> idMap = exportImportDao.queryQutoFieldId(listDatas, fieldMap);
        return idMap;
    }

    /**
     * 导入excel数据时将引用字段中文名称替换成对应的id
     * @author               xiaogq
     * @param listDatas      读取excel文件中的数据
     * @param idMap          根据引用字段中文名称查询出的id集合
     * @param fieldMap       引用字段信息集合
     * @return
     */
    public List<Map<String, Object>> returnInsertListDatas(List<Map<String, Object>> listDatas, Map<String, List<Map<String, Object>>> idMap, List<Map<String, String>> fieldMap){
        List<Map<String, Object>> insertListDatas = getSaveOtherEntityListDatas(listDatas, idMap, fieldMap);
        return insertListDatas;
    }

    /**
     * 导入excel数据时将引用字段中文名称替换成对应的id
     * @author               xiaogq
     * @param listDatas      读取excel文件中的数据
     * @param idMap          根据引用字段中文名称查询出的id集合
     * @param fieldMap       引用字段信息集合
     * @return
     */
    public List<Map<String, Object>> getSaveOtherEntityListDatas(List<Map<String, Object>> listDatas, Map<String, List<Map<String, Object>>> idMap, List<Map<String, String>> fieldMap){
        List<Map<String, Object>> insertListDatas = new ArrayList<Map<String, Object>>();
        insertListDatas.add(ExportUtils.getImportTitle(fieldMap));
        for (int i = 1; i < listDatas.size(); i++) {
            Map<String, Object> dataMap = listDatas.get(i);
            Map<String,Object> valueMap = new LinkedMap();
            for (Map<String, String> fieldmap : fieldMap){
                String fieldName = (fieldmap.get("fieldName")).trim();
                String fieldId = (fieldmap.get("fieldid")).trim();
                String newname = "";
                String index = (fieldmap.get("index")).trim();
                String dataName = String.valueOf(dataMap.get(index)).trim();
                boolean exist = true;
                if ((fieldName.toUpperCase().startsWith("PK_")) || (fieldName.toUpperCase().endsWith("_SINGLEBOX")) || (fieldName.toUpperCase().endsWith("_RADIO")) || (fieldName.toUpperCase().endsWith("_ENTITY"))) {
                    List<Map<String, Object>> listName = idMap.get(index);
                    if (listName.size() > 0) {
                        for (Map<String, Object> namMap : listName) {
                            if (!dataName.equals("")) {
                                if (dataName.equals(String.valueOf(namMap.get("name")).trim())) {
                                    newname = namMap.get("id").toString();
                                    exist = false;
                                    break;
                                }
                            }else {
                                exist = false;
                            }
                        }
                    } else{
                        exist = false;
                        if (!dataName.equals("")) {
                            exist = true;
                        }
                    }
                    if (exist) {
                        newname = "error";
                    }
                    valueMap.put(index, newname);
                } else if (fieldName.toUpperCase().endsWith("BIT")) {
                    if (dataName.equals("是") || dataName.equals("停用")) {
                        valueMap.put(index, 1);
                    }else{
                        valueMap.put(index, 0);
                    }
                } else if ((fieldName.toUpperCase().endsWith("_INT")) || (fieldName.toUpperCase().endsWith("_DECIMAL")) ||
                        (fieldName.toUpperCase().endsWith("_CURRENCY")) || (fieldName.toUpperCase().endsWith("_PRECENT")) ||
                        (fieldName.toUpperCase().endsWith("_LONG"))) {
                    if ((dataName != null) && (!dataName.equals(""))) {
                        valueMap.put(index, dataName);
                    }else {
                        valueMap.put(index, 0);
                    }
                } else {
                    valueMap.put(index,dataName);
                }
            }
            insertListDatas.add(valueMap);
        }
        return insertListDatas;
    }

    /**
     * 导入excel数据验证数据是否合法方法
     * @author              xiaogq
     * @param listDatas     excel原数据
     * @param newDatas      excel新数据
     * @param fieldMap      需要导入的字段信息
     * @param entityId      实体id
     * @param emp           当前登录系统的员工信息
     * @return
     */
    public Map<String, Object> verlidataImport(List<Map<String,Object>> listDatas,List<Map<String,Object>> newDatas,List<Map<String,String>> fieldMap,String entityId,Employee emp){
        boolean bool = false;
        //根据实体id查询实体所有的实体类型
        List<String> typesList = entityTypeDao.getEntityTypesByEntityId(entityId);
        //存放验证通过的数据集合
        List<Map<String,Object>> succesDatas = new ArrayList<Map<String,Object>>();
        //存放验证失败的数据集合
        List<Map<String, Object>> errorDatas = new ArrayList<Map<String, Object>>();
        List otherFileNameList = new ArrayList();
        List fileNameList = new ArrayList();
        //存放用户登录名数据集合
        List sysNameList = new ArrayList();
        //如果是员工实体
        if (Integer.parseInt(entityId) == TableConstant.EMPLOYEE_TCODE) {
            sysNameList = exportImportDao.getEmpLoginNames(entityId);
        }

        Map<String, Object> errorMap = new HashMap<String, Object>();
        //导入实体数据时添加错误列头
        Map<String, Object> celMap = returnCells(listDatas.get(0));
        errorDatas.add(celMap);
        for (int k = 1; k < newDatas.size(); k++) {
            Map<String,Object> impMap = new LinkedMap();
            Map<String, Object> expMap = new LinkedMap();
            StringBuffer strBuffer = new StringBuffer();
            int cols = 0; int index = 0;
            boolean globalBoo = true;
            Map<String, Object> oldMap = listDatas.get(k);
            Map<String, Object> dataMap = newDatas.get(k);
            for (String key : oldMap.keySet()) {
                if (!key.trim().equals("0")) {
                    cols++;
                    String celOldValue = String.valueOf(oldMap.get(key));
                    if (dataMap.containsKey(key)) {
                        boolean boo = true;
                        //获取字段英文名称
                        String columName = fieldMap.get(index).get("fieldName").trim();
                        if (columName.indexOf("__") != -1) {
                            columName = columName.split("__")[1];
                        }
                        //获取导入字段是否必填标识
                        String required = fieldMap.get(index).get("required").trim();
                        //获取导入字段是否唯一标识
                        String vunique = fieldMap.get(index).get("vunique").trim();
                        //获取导入字段中文名称
                        String inchinese = fieldMap.get(index).get("inchinese").trim();
                        //获取字段类型
                        String type = ExportUtils.returnFieldType(columName);
                        //获取字段对应数据库中字段类型
                        String numtype = FieldTypeParse.newInstance().getSFieldType(type).getDatabaseType().getDataBaseType();
                        //获取数据验证规则
                        String javaRegular = FieldTypeParse.newInstance().getSFieldType(type).getJavaRegular();
                        //System.out.println("columName==="+columName+"&type=="+type+"&numtype=="+numtype+"&javaRegular=="+javaRegular);
                        //获取excel列中的值
                        String celValue = String.valueOf(dataMap.get(key)).trim();
                        //验证必填字段是否为空
                        if (required.equals("true")) {
                            boo = ExportUtils.fieldValueIsNull(celValue);
                            if (!boo) {
                                strBuffer.append("第" + cols + "列'" + inchinese + "'为必填项不能为空;");
                                globalBoo = false;
                            }
                        }
                        //验证取值唯一的字段是否取值唯一
                        if (vunique.equals("true")) {
                            boo = fieldValueIsUnique(entityId, columName, celOldValue, otherFileNameList, cols);
                            if (!boo) {
                                strBuffer.append("第" + cols + "列'" + inchinese + "'取值为一不能重复;");
                                globalBoo = false;
                            }
                        }
                        if (numtype.equals("varchar")){
                            if (Integer.parseInt(entityId) == TableConstant.EMPLOYEE_TCODE && columName.equals("job_number_varchar")){
                                if (sysNameList.contains(celValue)) {
                                    strBuffer.append("第" + cols + "列用户登录名重复;");
                                    globalBoo = false;
                                }else if (fileNameList.contains(celValue)) {
                                    strBuffer.append("文件中第" + cols + "列用户登录名重复;");
                                    globalBoo = false;
                                } else {
                                    fileNameList.add(celValue);
                                }
                            }else if (columName.startsWith("pk_")) {
                                if (celValue.equals("error")) {
                                    strBuffer.append("第" + cols + "列'" + inchinese + "'引用数据不存在;");
                                    globalBoo = false;
                                }
                            } else {
                                boo = RegularExpressionValidation.isVarchar(celValue, javaRegular);
                                if (!boo) {
                                    strBuffer.append("第" + cols + "列'" + inchinese + "'数据格式错误;");
                                    globalBoo = false;
                                }
                            }
                        } else if (numtype.equals("bit")) {
                            boo = RegularExpressionValidation.isBit(celValue, javaRegular);
                            if (!boo) {
                                strBuffer.append("第" + cols + "列'" + inchinese + "'数据格式错误;");
                                globalBoo = false;
                            }
                        } else if ((numtype.equals("datetime")) || (numtype.equals("timestamp")) || (numtype.equals("date"))) {
                            boo = RegularExpressionValidation.isDatetime(celValue);
                            if (!boo) {
                                strBuffer.append("第" + cols + "列'" + inchinese + "'数据格式错误;");
                                globalBoo = false;
                            }
                        } else if (numtype.equals("int")){
                            if (columName.startsWith("pk_")) {
                                if (celValue.equals("error")) {
                                    strBuffer.append("第" + cols + "列'" + inchinese + "'引用数据不存在;");
                                    globalBoo = false;
                                }
                                if (columName.equals("pk_sys_custom_entity_type_1")){
                                    if (!typesList.contains(celValue)) {
                                        strBuffer.append("第" + cols + "列'" + inchinese + "'与导入实体类型不一致;");
                                        globalBoo = false;
                                    }
                                }
                            } else if ((columName.endsWith("_radio")) || (columName.endsWith("_singlebox"))) {
                                if (celValue.equals("error")) {
                                    strBuffer.append("第" + cols + "列'" + inchinese + "'枚举值不存在;");
                                    globalBoo = false;
                                } else {
                                    boo = RegularExpressionValidation.isInt(celValue, javaRegular);
                                    if (!boo) {
                                        strBuffer.append("第" + cols + "列'" + inchinese + "'枚举值只能是数值;");
                                        globalBoo = false;
                                    }
                                }
                            }else if (celValue.equals("error")) {
                                strBuffer.append("第" + cols + "列'" + inchinese + "'值在系统中不存在;");
                                globalBoo = false;
                            } else {
                                boo = RegularExpressionValidation.isInt(celValue, javaRegular);
                                if (!boo) {
                                    strBuffer.append("第" + cols + "列'" + inchinese + "'数据只能是整数;");
                                    globalBoo = false;
                                }
                            }
                        }else if (numtype.equals("decimal")) {
                            boo = RegularExpressionValidation.isDouble(celValue, javaRegular);
                            if (!boo) {
                                strBuffer.append("第" + cols + "列'" + inchinese + "'数据只能是数字;");
                                globalBoo = false;
                            }
                        } else if (numtype.equals("long")) {
                            boo = RegularExpressionValidation.isInt(celValue, javaRegular);
                            if (!boo) {
                                strBuffer.append("第" + cols + "列'" + inchinese + "'数据只能是整数;");
                                globalBoo = false;
                            }
                        } else {
                            boo = RegularExpressionValidation.isVarchar(celValue, javaRegular);
                            if (!boo) {
                                strBuffer.append("第" + cols + "列'" + inchinese + "'数据格式错误;");
                                globalBoo = false;
                            }
                        }
                        impMap.put(key, celValue);
                        expMap.put(key, celOldValue);
                        index++;
                    } else {
                        expMap.put(key, celOldValue);
                    }
                }
            }
            expMap.put(String.valueOf(cols + 1), strBuffer.toString());
            if (k != 0) {
                if (!globalBoo) {
                    bool = true;
                    errorDatas.add(expMap);
                    errorMap.put("values", errorDatas);
                } else {
                    succesDatas.add(impMap);
                }
            }
        }
        //批量保存导入的数据
        exportImportDao.batchAddImportDate(succesDatas,fieldMap,entityId,emp);
        errorMap.put("bool", Boolean.valueOf(bool));
        errorMap.put("successNumber", Integer.valueOf(succesDatas.size()));
        errorMap.put("allNumber", Integer.valueOf(newDatas.size() - 1));
        return errorMap;
    }

    /**
     * 导入实体数据时添加错误列头
     * @author            xiaogq
     * @param cellsMap    excel列头
     * @return
     */
    public Map<String, Object> returnCells(Map<String, Object> cellsMap) {
        StringBuffer errocCells = new StringBuffer();
        Map celMap = new LinkedMap();
        int col = 0;
        for (String key : cellsMap.keySet()) {
            if (!key.equals("0")) {
                col++;
                celMap.put(key, cellsMap.get(key));
            }
        }
        celMap.put(String.valueOf(col + 1), "导入错误信息");
        return celMap;
    }

    /**
     * 验证导入的实体数据值是否唯一
     * @author             xiaogq
     * @param entityId     实体id
     * @param fieldName    字段名称
     * @param fieldValue   字段值
     * @return
     */
    public boolean fieldIsUnique(String entityId, String fieldName, String fieldValue){
        return this.exportImportDao.fieldIsUnique(entityId, fieldName, fieldValue);
    }

    /**
     * 如果是取值唯一的数据验证导入的数据是否唯一
     * @author                   xiaogq
     * @param entityId           实体id
     * @param columName          字段名称
     * @param celValue           字段值
     * @param otherFileNameList
     * @param cols
     * @return
     */
    public boolean fieldValueIsUnique(String entityId, String columName, String celValue, List<String> otherFileNameList, int cols){
        boolean bool = true;
        if (!celValue.equals("")){
            boolean ifUnique = fieldIsUnique(entityId, columName, celValue);
            if (ifUnique) {
                bool = false;
            }else if (otherFileNameList.contains(celValue + cols)) {
                bool = false;
            }else {
                otherFileNameList.add(celValue + cols);
            }
        }
        return bool;
    }

}
